{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "30f928fc-b9e1-4122-9672-ba8a8ae747ec",
   "metadata": {},
   "source": [
    "#### Objective:- Create table for generating race migration points."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b0820f9a-3178-4e99-a8d6-6c85f31780fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "import ast,os,random\n",
    "pd.set_option('display.float_format','{:.1f}'.format)\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "import cudf, cupy as cp\n",
    "import numpy as np\n",
    "import time\n",
    "# pd.set_option('display.max_colwidth', -1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "280acc85-769e-4104-a6ad-e995ce78d59e",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Race computation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "4a760468-8fe9-4e05-a2bc-d8be081069a7",
   "metadata": {},
   "outputs": [],
   "source": [
    "full_2020_path='data/nhgis0007_csv/nhgis0007_ds248_2020_block.csv'\n",
    "full_2010_path='data/race2010/nhgis0004_csv/nhgis0004_ds172_2010_block.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "a774aff9-5e20-493f-a4d3-96a48ccc33fd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GISJOIN</th>\n",
       "      <th>STATEA</th>\n",
       "      <th>H7X001</th>\n",
       "      <th>H7X002</th>\n",
       "      <th>H7X003</th>\n",
       "      <th>H7X004</th>\n",
       "      <th>H7X005</th>\n",
       "      <th>H7X006</th>\n",
       "      <th>H7X007</th>\n",
       "      <th>H7X008</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>G01000100201001000</td>\n",
       "      <td>01</td>\n",
       "      <td>61</td>\n",
       "      <td>55</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>G01000100201001001</td>\n",
       "      <td>01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>G01000100201001002</td>\n",
       "      <td>01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>G01000100201001003</td>\n",
       "      <td>01</td>\n",
       "      <td>75</td>\n",
       "      <td>66</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>G01000100201001004</td>\n",
       "      <td>01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              GISJOIN STATEA  H7X001  H7X002  H7X003  H7X004  H7X005  H7X006  \\\n",
       "0  G01000100201001000     01      61      55       4       0       1       0   \n",
       "1  G01000100201001001     01       0       0       0       0       0       0   \n",
       "2  G01000100201001002     01       0       0       0       0       0       0   \n",
       "3  G01000100201001003     01      75      66       4       0       0       0   \n",
       "4  G01000100201001004     01       0       0       0       0       0       0   \n",
       "\n",
       "   H7X007  H7X008  \n",
       "0       0       1  \n",
       "1       0       0  \n",
       "2       0       0  \n",
       "3       4       1  \n",
       "4       0       0  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "race_10 = cudf.read_csv(full_2010_path,usecols=['GISJOIN','STATEA','H7X001','H7X002','H7X003','H7X004','H7X005','H7X006','H7X007','H7X008'])\n",
    "race_10.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "08e5051c-3f88-42fe-94f0-b01e70d91b1b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GISJOIN</th>\n",
       "      <th>STATE</th>\n",
       "      <th>P10</th>\n",
       "      <th>R101</th>\n",
       "      <th>R102</th>\n",
       "      <th>R103</th>\n",
       "      <th>R104</th>\n",
       "      <th>R105</th>\n",
       "      <th>R106</th>\n",
       "      <th>R107</th>\n",
       "      <th>ID10</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01000100201001000</td>\n",
       "      <td>01</td>\n",
       "      <td>61</td>\n",
       "      <td>55</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>10010201001000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01000100201001001</td>\n",
       "      <td>01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10010201001001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01000100201001002</td>\n",
       "      <td>01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10010201001002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01000100201001003</td>\n",
       "      <td>01</td>\n",
       "      <td>75</td>\n",
       "      <td>66</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>10010201001003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01000100201001004</td>\n",
       "      <td>01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10010201001004</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             GISJOIN STATE  P10  R101  R102  R103  R104  R105  R106  R107  \\\n",
       "0  01000100201001000    01   61    55     4     0     1     0     0     1   \n",
       "1  01000100201001001    01    0     0     0     0     0     0     0     0   \n",
       "2  01000100201001002    01    0     0     0     0     0     0     0     0   \n",
       "3  01000100201001003    01   75    66     4     0     0     0     4     1   \n",
       "4  01000100201001004    01    0     0     0     0     0     0     0     0   \n",
       "\n",
       "             ID10  \n",
       "0  10010201001000  \n",
       "1  10010201001001  \n",
       "2  10010201001002  \n",
       "3  10010201001003  \n",
       "4  10010201001004  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "race_10.GISJOIN = race_10.GISJOIN.str.replace('G', '')\n",
    "race_10.rename(columns={'H7X001':'P10','STATEA':'STATE','H7X002':'R101','H7X003':'R102','H7X004':'R103','H7X005':'R104','H7X006':'R105','H7X007':'R106','H7X008':'R107'},inplace=True)\n",
    "race_10['ID10'] = (race_10.GISJOIN.str.slice(start=0,stop=2) + race_10.GISJOIN.str.slice(start=3,stop=6) + race_10.GISJOIN.str.slice(start=7)).astype('int64')\n",
    "race_10.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6d17d79-12b9-447a-9ea9-b578c99c0154",
   "metadata": {},
   "source": [
    "#### Prepare 2020 data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "a5802313-368c-46ac-b351-207f86484863",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GISJOIN</th>\n",
       "      <th>GEOCODE</th>\n",
       "      <th>STATEA</th>\n",
       "      <th>U7B001</th>\n",
       "      <th>U7B003</th>\n",
       "      <th>U7B004</th>\n",
       "      <th>U7B005</th>\n",
       "      <th>U7B006</th>\n",
       "      <th>U7B007</th>\n",
       "      <th>U7B008</th>\n",
       "      <th>U7B009</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>G01000100201001000</td>\n",
       "      <td>010010201001000</td>\n",
       "      <td>01</td>\n",
       "      <td>21.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>G01000100201001001</td>\n",
       "      <td>010010201001001</td>\n",
       "      <td>01</td>\n",
       "      <td>34.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>G01000100201001002</td>\n",
       "      <td>010010201001002</td>\n",
       "      <td>01</td>\n",
       "      <td>29.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>G01000100201001003</td>\n",
       "      <td>010010201001003</td>\n",
       "      <td>01</td>\n",
       "      <td>17.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>G01000100201001004</td>\n",
       "      <td>010010201001004</td>\n",
       "      <td>01</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              GISJOIN          GEOCODE STATEA  U7B001  U7B003  U7B004  U7B005  \\\n",
       "0  G01000100201001000  010010201001000     01    21.0    12.0     4.0     0.0   \n",
       "1  G01000100201001001  010010201001001     01    34.0    18.0    11.0     0.0   \n",
       "2  G01000100201001002  010010201001002     01    29.0    24.0     2.0     0.0   \n",
       "3  G01000100201001003  010010201001003     01    17.0    16.0     0.0     0.0   \n",
       "4  G01000100201001004  010010201001004     01     0.0     0.0     0.0     0.0   \n",
       "\n",
       "   U7B006  U7B007  U7B008  U7B009  \n",
       "0     0.0     0.0     0.0     5.0  \n",
       "1     2.0     0.0     1.0     2.0  \n",
       "2     0.0     0.0     2.0     1.0  \n",
       "3     0.0     0.0     0.0     1.0  \n",
       "4     0.0     0.0     0.0     0.0  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "race_20 = cudf.read_csv(full_2020_path,usecols=['GISJOIN','GEOCODE','STATEA','U7B001','U7B003','U7B004','U7B005','U7B006','U7B007','U7B008','U7B009'],dtype={'U7B001':'float32','U7B003':'float32','U7B004':'float32','U7B005':'float32','U7B006':'float32','U7B007':'float32','U7B008':'float32','U7B009':'float32'})\n",
    "race_20.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "376a6c89-4981-48b3-8d89-b0651361e457",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GISJOIN</th>\n",
       "      <th>ID20</th>\n",
       "      <th>STATE</th>\n",
       "      <th>P20</th>\n",
       "      <th>R201</th>\n",
       "      <th>R202</th>\n",
       "      <th>R203</th>\n",
       "      <th>R204</th>\n",
       "      <th>R205</th>\n",
       "      <th>R206</th>\n",
       "      <th>R207</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000100201001000</td>\n",
       "      <td>010010201001000</td>\n",
       "      <td>01</td>\n",
       "      <td>21.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000100201001001</td>\n",
       "      <td>010010201001001</td>\n",
       "      <td>01</td>\n",
       "      <td>34.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1000100201001002</td>\n",
       "      <td>010010201001002</td>\n",
       "      <td>01</td>\n",
       "      <td>29.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1000100201001003</td>\n",
       "      <td>010010201001003</td>\n",
       "      <td>01</td>\n",
       "      <td>17.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1000100201001004</td>\n",
       "      <td>010010201001004</td>\n",
       "      <td>01</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            GISJOIN             ID20 STATE  P20  R201  R202  R203  R204  R205  \\\n",
       "0  1000100201001000  010010201001000    01 21.0  12.0   4.0   0.0   0.0   0.0   \n",
       "1  1000100201001001  010010201001001    01 34.0  18.0  11.0   0.0   2.0   0.0   \n",
       "2  1000100201001002  010010201001002    01 29.0  24.0   2.0   0.0   0.0   0.0   \n",
       "3  1000100201001003  010010201001003    01 17.0  16.0   0.0   0.0   0.0   0.0   \n",
       "4  1000100201001004  010010201001004    01  0.0   0.0   0.0   0.0   0.0   0.0   \n",
       "\n",
       "   R206  R207  \n",
       "0   0.0   5.0  \n",
       "1   1.0   2.0  \n",
       "2   2.0   1.0  \n",
       "3   0.0   1.0  \n",
       "4   0.0   0.0  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df = small.copy()\n",
    "race_20.rename(columns={'U7B001':'P20','STATEA':'STATE','GEOCODE':'ID20','U7B003':'R201','U7B004':'R202','U7B005':'R203','U7B006':'R204','U7B007':'R205','U7B008':'R206','U7B009':'R207'},inplace=True)\n",
    "race_20.GISJOIN = race_20.GISJOIN.str.replace('G', '').astype('int64')\n",
    "race_20.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf8efb3b-2209-4834-ae4e-90ad4c58ff6e",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Mapper "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b0e95e52-b1cc-4f26-b87e-e4007907abf8",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Concat mapper files for states"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "0801d4e7-475a-4b96-ae66-b32cd1f97639",
   "metadata": {},
   "outputs": [],
   "source": [
    "states = {1 :\"AL\",2 :\"AK\",4 :\"AZ\",5 :\"AR\",6 :\"CA\",8 :\"CO\",9 :\"CT\",10:\"DE\",11:\"DC\",12:\"FL\",13:\"GA\",15:\"HI\",\n",
    "          16:\"ID\",17:\"IL\",18:\"IN\",19:\"IA\",20:\"KS\",21:\"KY\",22:\"LA\",23:\"ME\",24:\"MD\",25:\"MA\",26:\"MI\",27:\"MN\",\n",
    "          28:\"MS\",29:\"MO\",30:\"MT\",31:\"NE\",32:\"NV\",33:\"NH\",34:\"NJ\",35:\"NM\",36:\"NY\",37:\"NC\",38:\"ND\",39:\"OH\",\n",
    "          40:\"OK\",41:\"OR\",42:\"PA\",44:\"RI\",45:\"SC\",46:\"SD\",47:\"TN\",48:\"TX\",49:\"UT\",50:\"VT\",51:\"VA\",53:\"WA\",\n",
    "          54:\"WV\",55:\"WI\",56:\"WY\",72:\"PR\"}\n",
    "\n",
    "# states = {11:\"DC\",10:\"DE\"}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "617738b3-013c-44e3-99fc-b66054be5e0b",
   "metadata": {},
   "outputs": [],
   "source": [
    "def concat_states_mapper(state_key_list): \n",
    "    \n",
    "    concat_mapper = cudf.DataFrame()\n",
    "    for i in state_key_list:\n",
    "        if i< 10:\n",
    "            i_str = '0'+str(i)\n",
    "        else:\n",
    "            i_str = str(i)\n",
    "        path = 'data/block_rel_files/tab2010_tab2020_st%s_%s.csv'%(i_str,states[i].lower())\n",
    "        if os.path.isfile(path):    \n",
    "            mapper = cudf.read_csv(path,delimiter='|')\n",
    "            concat_mapper = cudf.concat([concat_mapper,mapper])\n",
    "        else:\n",
    "            print(\"mapper file does not exist\")\n",
    "            continue\n",
    "    return concat_mapper"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "e85d533d-00bc-462a-ad80-d5ddbe6a23fa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATE_2010</th>\n",
       "      <th>COUNTY_2010</th>\n",
       "      <th>TRACT_2010</th>\n",
       "      <th>BLK_2010</th>\n",
       "      <th>BLKSF_2010</th>\n",
       "      <th>AREALAND_2010</th>\n",
       "      <th>AREAWATER_2010</th>\n",
       "      <th>BLOCK_PART_FLAG_O</th>\n",
       "      <th>STATE_2020</th>\n",
       "      <th>COUNTY_2020</th>\n",
       "      <th>TRACT_2020</th>\n",
       "      <th>BLK_2020</th>\n",
       "      <th>BLKSF_2020</th>\n",
       "      <th>AREALAND_2020</th>\n",
       "      <th>AREAWATER_2020</th>\n",
       "      <th>BLOCK_PART_FLAG_R</th>\n",
       "      <th>AREALAND_INT</th>\n",
       "      <th>AREAWATER_INT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>20100</td>\n",
       "      <td>1000</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>482628</td>\n",
       "      <td>0</td>\n",
       "      <td>p</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>20100</td>\n",
       "      <td>1000</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>288702</td>\n",
       "      <td>0</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>288702</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>20100</td>\n",
       "      <td>1000</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>482628</td>\n",
       "      <td>0</td>\n",
       "      <td>p</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>20100</td>\n",
       "      <td>1001</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>194408</td>\n",
       "      <td>0</td>\n",
       "      <td>p</td>\n",
       "      <td>193926</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   STATE_2010  COUNTY_2010  TRACT_2010  BLK_2010 BLKSF_2010  AREALAND_2010  \\\n",
       "0           1            1       20100      1000       <NA>         482628   \n",
       "1           1            1       20100      1000       <NA>         482628   \n",
       "\n",
       "   AREAWATER_2010 BLOCK_PART_FLAG_O  STATE_2020  COUNTY_2020  TRACT_2020  \\\n",
       "0               0                 p           1            1       20100   \n",
       "1               0                 p           1            1       20100   \n",
       "\n",
       "   BLK_2020 BLKSF_2020  AREALAND_2020  AREAWATER_2020 BLOCK_PART_FLAG_R  \\\n",
       "0      1000       <NA>         288702               0              <NA>   \n",
       "1      1001       <NA>         194408               0                 p   \n",
       "\n",
       "   AREALAND_INT  AREAWATER_INT  \n",
       "0        288702              0  \n",
       "1        193926              0  "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mapper_df = concat_states_mapper(states.keys())\n",
    "mapper_df.rename(columns={'﻿STATE_2010':'STATE_2010'},inplace=True)\n",
    "mapper_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "f379da5b-9eda-448c-a3ca-eb0b275f70a5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>ID10</th>\n",
       "      <th>ID20</th>\n",
       "      <th>STATE_2020</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>10010201001000</td>\n",
       "      <td>10010201001000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>10010201001000</td>\n",
       "      <td>10010201001001</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>10010201001001</td>\n",
       "      <td>10010201001001</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>10010201001002</td>\n",
       "      <td>10010201001002</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>10010201001003</td>\n",
       "      <td>10010201001002</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index            ID10            ID20  STATE_2020\n",
       "0      0  10010201001000  10010201001000           1\n",
       "1      1  10010201001000  10010201001001           1\n",
       "2      2  10010201001001  10010201001001           1\n",
       "3      3  10010201001002  10010201001002           1\n",
       "4      4  10010201001003  10010201001002           1"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mapper_df['ID10'] = (mapper_df.STATE_2010.astype('str').str.rjust(2,'0') + mapper_df.COUNTY_2010.astype('str').str.rjust(3,'0') + mapper_df.TRACT_2010.astype('str').str.rjust(6,'0') + mapper_df.BLK_2010.astype('str').str.rjust(4,'0')).astype('int64')\n",
    "mapper_df['ID20'] = (mapper_df.STATE_2020.astype('str').str.rjust(2,'0') + mapper_df.COUNTY_2020.astype('str').str.rjust(3,'0') + mapper_df.TRACT_2020.astype('str').str.rjust(6,'0') + mapper_df.BLK_2020.astype('str').str.rjust(4,'0')).astype('int64')\n",
    "mapper_df = mapper_df[['ID10','ID20','STATE_2020']].reset_index()\n",
    "mapper_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf542643-b6ed-44d7-b82e-b83fd89a4fcf",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Create Mapped IDs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "258cf1be-162b-4f44-a6e4-bb2b3e77c99e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID10</th>\n",
       "      <th>P10</th>\n",
       "      <th>R101</th>\n",
       "      <th>R102</th>\n",
       "      <th>R103</th>\n",
       "      <th>R104</th>\n",
       "      <th>R105</th>\n",
       "      <th>R106</th>\n",
       "      <th>R107</th>\n",
       "      <th>freq</th>\n",
       "      <th>P10_weights</th>\n",
       "      <th>R101_weights</th>\n",
       "      <th>R102_weights</th>\n",
       "      <th>R103_weights</th>\n",
       "      <th>R104_weights</th>\n",
       "      <th>R105_weights</th>\n",
       "      <th>R106_weights</th>\n",
       "      <th>R107_weights</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2832</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>61.0</td>\n",
       "      <td>55</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>30.5</td>\n",
       "      <td>27.5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2833</th>\n",
       "      <td>10010201001001</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2834</th>\n",
       "      <td>10010201001002</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2835</th>\n",
       "      <td>10010201001003</td>\n",
       "      <td>75.0</td>\n",
       "      <td>66</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>37.5</td>\n",
       "      <td>33.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2836</th>\n",
       "      <td>10010201001004</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                ID10  P10  R101  R102  R103  R104  R105  R106  R107  freq  \\\n",
       "2832  10010201001000 61.0    55     4     0     1     0     0     1     2   \n",
       "2833  10010201001001  0.0     0     0     0     0     0     0     0     1   \n",
       "2834  10010201001002  0.0     0     0     0     0     0     0     0     1   \n",
       "2835  10010201001003 75.0    66     4     0     0     0     4     1     2   \n",
       "2836  10010201001004  0.0     0     0     0     0     0     0     0     1   \n",
       "\n",
       "      P10_weights  R101_weights  R102_weights  R103_weights  R104_weights  \\\n",
       "2832         30.5          27.5           2.0           0.0           0.5   \n",
       "2833          0.0           0.0           0.0           0.0           0.0   \n",
       "2834          0.0           0.0           0.0           0.0           0.0   \n",
       "2835         37.5          33.0           2.0           0.0           0.0   \n",
       "2836          0.0           0.0           0.0           0.0           0.0   \n",
       "\n",
       "      R105_weights  R106_weights  R107_weights  \n",
       "2832           0.0           0.0           0.5  \n",
       "2833           0.0           0.0           0.0  \n",
       "2834           0.0           0.0           0.0  \n",
       "2835           0.0           2.0           0.5  \n",
       "2836           0.0           0.0           0.0  "
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "freq_df = mapper_df.ID10.value_counts().reset_index().sort_values('index')\n",
    "freq_df.rename(columns={'ID10':'freq'},inplace=True)\n",
    "freq_df.rename(columns={'index':'ID10'},inplace=True)\n",
    "freq_df = freq_df.reset_index(drop=True)\n",
    "race_10.P10 = race_10.P10.astype('float32')\n",
    "freq_df = cudf.merge(freq_df,race_10[['ID10','P10','R101','R102','R103','R104','R105','R106','R107']],on='ID10',how='right').sort_values('ID10')\n",
    "freq_df['P10_weights'] = freq_df['P10'].divide(freq_df['freq'])\n",
    "freq_df['R101_weights'] = freq_df['R101'].divide(freq_df['freq'])\n",
    "freq_df['R102_weights'] = freq_df['R102'].divide(freq_df['freq'])\n",
    "freq_df['R103_weights'] = freq_df['R103'].divide(freq_df['freq'])\n",
    "freq_df['R104_weights'] = freq_df['R104'].divide(freq_df['freq'])\n",
    "freq_df['R105_weights'] = freq_df['R105'].divide(freq_df['freq'])\n",
    "freq_df['R106_weights'] = freq_df['R106'].divide(freq_df['freq'])\n",
    "freq_df['R107_weights'] = freq_df['R107'].divide(freq_df['freq'])\n",
    "freq_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "96ff3dba-d0e2-4d83-b118-1407b36f0ee4",
   "metadata": {},
   "outputs": [],
   "source": [
    "weighted_mapper = cudf.merge(mapper_df,freq_df[['ID10','P10_weights','R101_weights','R102_weights','R103_weights','R104_weights','R105_weights','R106_weights','R107_weights']],on='ID10',how='left').sort_values('ID20').reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "923988bc-2f1e-4257-86d3-59598f6e1cca",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID20</th>\n",
       "      <th>ID10</th>\n",
       "      <th>eq_P10</th>\n",
       "      <th>eq_R101</th>\n",
       "      <th>eq_R102</th>\n",
       "      <th>eq_R103</th>\n",
       "      <th>eq_R104</th>\n",
       "      <th>eq_R105</th>\n",
       "      <th>eq_R106</th>\n",
       "      <th>eq_R107</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>10010201001000</td>\n",
       "      <td>30.5</td>\n",
       "      <td>27.5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10010201001001</td>\n",
       "      <td>20020402002001</td>\n",
       "      <td>30.5</td>\n",
       "      <td>27.5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10010201001002</td>\n",
       "      <td>50051005005035</td>\n",
       "      <td>51.8</td>\n",
       "      <td>45.3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10010201001003</td>\n",
       "      <td>40040804004046</td>\n",
       "      <td>13.3</td>\n",
       "      <td>11.3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10010201001004</td>\n",
       "      <td>10010201001008</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             ID20            ID10  eq_P10  eq_R101  eq_R102  eq_R103  eq_R104  \\\n",
       "0  10010201001000  10010201001000    30.5     27.5      2.0      0.0      0.5   \n",
       "1  10010201001001  20020402002001    30.5     27.5      2.0      0.0      0.5   \n",
       "2  10010201001002  50051005005035    51.8     45.3      4.0      0.0      0.0   \n",
       "3  10010201001003  40040804004046    13.3     11.3      2.0      0.0      0.0   \n",
       "4  10010201001004  10010201001008     0.0      0.0      0.0      0.0      0.0   \n",
       "\n",
       "   eq_R105  eq_R106  eq_R107  \n",
       "0      0.0      0.0      0.5  \n",
       "1      0.0      0.0      0.5  \n",
       "2      0.0      2.0      0.5  \n",
       "3      0.0      0.0      0.0  \n",
       "4      0.0      0.0      0.0  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eq_10 = weighted_mapper.groupby('ID20')[['ID10','P10_weights','R101_weights','R102_weights','R103_weights','R104_weights','R105_weights','R106_weights','R107_weights']].sum().reset_index().sort_values('ID20').reset_index(drop=True) \n",
    "del(weighted_mapper)\n",
    "eq_10.rename(columns={'P10_weights':'eq_P10','R101_weights':'eq_R101','R102_weights':'eq_R102','R103_weights':'eq_R103','R104_weights':'eq_R104','R105_weights':'eq_R105','R106_weights':'eq_R106','R107_weights':'eq_R107'},inplace=True)\n",
    "eq_10.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "da7484fd-a7ba-4b88-a78a-f71607d1bd62",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GISJOIN</th>\n",
       "      <th>ID20</th>\n",
       "      <th>STATE</th>\n",
       "      <th>P20</th>\n",
       "      <th>R201</th>\n",
       "      <th>R202</th>\n",
       "      <th>R203</th>\n",
       "      <th>R204</th>\n",
       "      <th>R205</th>\n",
       "      <th>R206</th>\n",
       "      <th>R207</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000100201001000</td>\n",
       "      <td>010010201001000</td>\n",
       "      <td>01</td>\n",
       "      <td>21.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000100201001001</td>\n",
       "      <td>010010201001001</td>\n",
       "      <td>01</td>\n",
       "      <td>34.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            GISJOIN             ID20 STATE  P20  R201  R202  R203  R204  R205  \\\n",
       "0  1000100201001000  010010201001000    01 21.0  12.0   4.0   0.0   0.0   0.0   \n",
       "1  1000100201001001  010010201001001    01 34.0  18.0  11.0   0.0   2.0   0.0   \n",
       "\n",
       "   R206  R207  \n",
       "0   0.0   5.0  \n",
       "1   1.0   2.0  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "race_20.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "db1de94f-ef6b-4fb0-b069-cd2de39003f2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8174955\n",
      "8174955\n"
     ]
    }
   ],
   "source": [
    "print(len(eq_10))\n",
    "print(len(race_20))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c32c9b67-bd59-45f5-a615-39f5dede0c6a",
   "metadata": {},
   "source": [
    "#### Compute net population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "7cbbb6ee-5142-4b34-926d-371950e1c3f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "race_df = eq_10[['ID20']].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "e16b9afe-7594-4a3d-8c40-ebec55c20c18",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID20</th>\n",
       "      <th>P_diff</th>\n",
       "      <th>R1_diff</th>\n",
       "      <th>R2_diff</th>\n",
       "      <th>R3_diff</th>\n",
       "      <th>R4_diff</th>\n",
       "      <th>R5_diff</th>\n",
       "      <th>R6_diff</th>\n",
       "      <th>R7_diff</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-9.5</td>\n",
       "      <td>-15.5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10010201001001</td>\n",
       "      <td>3.5</td>\n",
       "      <td>-9.5</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10010201001002</td>\n",
       "      <td>-22.8</td>\n",
       "      <td>-21.3</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10010201001003</td>\n",
       "      <td>3.7</td>\n",
       "      <td>4.7</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10010201001004</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             ID20  P_diff  R1_diff  R2_diff  R3_diff  R4_diff  R5_diff  \\\n",
       "0  10010201001000    -9.5    -15.5      2.0      0.0     -0.5      0.0   \n",
       "1  10010201001001     3.5     -9.5      9.0      0.0      1.5      0.0   \n",
       "2  10010201001002   -22.8    -21.3     -2.0      0.0      0.0      0.0   \n",
       "3  10010201001003     3.7      4.7     -2.0      0.0      0.0      0.0   \n",
       "4  10010201001004     0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "\n",
       "   R6_diff  R7_diff  \n",
       "0      0.0      4.5  \n",
       "1      1.0      1.5  \n",
       "2      0.0      0.5  \n",
       "3      0.0      1.0  \n",
       "4      0.0      0.0  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "race_df['P_diff'] = race_20['P20'].sub(eq_10['eq_P10'])\n",
    "race_df['R1_diff'] = race_20['R201'].sub(eq_10['eq_R101'])\n",
    "race_df['R2_diff'] = race_20['R202'].sub(eq_10['eq_R102'])\n",
    "race_df['R3_diff'] = race_20['R203'].sub(eq_10['eq_R103'])\n",
    "race_df['R4_diff'] = race_20['R204'].sub(eq_10['eq_R104'])\n",
    "race_df['R5_diff'] = race_20['R205'].sub(eq_10['eq_R105'])\n",
    "race_df['R6_diff'] = race_20['R206'].sub(eq_10['eq_R106'])\n",
    "race_df['R7_diff'] = race_20['R207'].sub(eq_10['eq_R107'])\n",
    "race_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "bc1a9e46-3e61-4b6a-aec0-288aa1b97ceb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GISJOIN</th>\n",
       "      <th>ID20</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>P20</th>\n",
       "      <th>eq_P10</th>\n",
       "      <th>P_delta</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000100201001000</td>\n",
       "      <td>10010201001000</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>21</td>\n",
       "      <td>30.5</td>\n",
       "      <td>-9.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000100201001001</td>\n",
       "      <td>10010201001001</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>34</td>\n",
       "      <td>30.5</td>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1000100201001002</td>\n",
       "      <td>10010201001002</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>29</td>\n",
       "      <td>51.8</td>\n",
       "      <td>-22.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1000100201001003</td>\n",
       "      <td>10010201001003</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>17</td>\n",
       "      <td>13.3</td>\n",
       "      <td>3.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1000100201001004</td>\n",
       "      <td>10010201001004</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            GISJOIN            ID20  STATE          COUNTY  P20  eq_P10  \\\n",
       "0  1000100201001000  10010201001000      1  Autauga County   21    30.5   \n",
       "1  1000100201001001  10010201001001      1  Autauga County   34    30.5   \n",
       "2  1000100201001002  10010201001002      1  Autauga County   29    51.8   \n",
       "3  1000100201001003  10010201001003      1  Autauga County   17    13.3   \n",
       "4  1000100201001004  10010201001004      1  Autauga County    0     0.0   \n",
       "\n",
       "   P_delta  \n",
       "0     -9.5  \n",
       "1      3.5  \n",
       "2    -22.8  \n",
       "3      3.7  \n",
       "4      0.0  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = cudf.read_csv('data/mapped_blocks_full.csv',dtype={'GISJOIN':'int64','ID20':'int64','STATE':'int32','COUNTY':'str','P20':'int32','P10_new':'int32'}).drop('Unnamed: 0',axis=1)\n",
    "df['P_delta']=df['P20'] - df['eq_P10']\n",
    "# df = df[df.number!=0] \n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "6c8c012b-461e-49d4-a071-cd5ccde4726a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8174955\n",
      "8174955\n"
     ]
    }
   ],
   "source": [
    "print(len(race_df))\n",
    "print(len(df))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "137cd8bd-983d-4d8e-9f65-250c5cf9c57f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID20</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>P_delta</th>\n",
       "      <th>R1_diff</th>\n",
       "      <th>R2_diff</th>\n",
       "      <th>R3_diff</th>\n",
       "      <th>R4_diff</th>\n",
       "      <th>R5_diff</th>\n",
       "      <th>R6_diff</th>\n",
       "      <th>R7_diff</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3392</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>-9.5</td>\n",
       "      <td>-15.5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3393</th>\n",
       "      <td>10010201001001</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>3.5</td>\n",
       "      <td>-9.5</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3394</th>\n",
       "      <td>10010201001002</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>-22.8</td>\n",
       "      <td>-21.3</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3395</th>\n",
       "      <td>10010201001003</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>3.7</td>\n",
       "      <td>4.7</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3396</th>\n",
       "      <td>10010201001004</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                ID20  STATE          COUNTY  P_delta  R1_diff  R2_diff  \\\n",
       "3392  10010201001000      1  Autauga County     -9.5    -15.5      2.0   \n",
       "3393  10010201001001      1  Autauga County      3.5     -9.5      9.0   \n",
       "3394  10010201001002      1  Autauga County    -22.8    -21.3     -2.0   \n",
       "3395  10010201001003      1  Autauga County      3.7      4.7     -2.0   \n",
       "3396  10010201001004      1  Autauga County      0.0      0.0      0.0   \n",
       "\n",
       "      R3_diff  R4_diff  R5_diff  R6_diff  R7_diff  \n",
       "3392      0.0     -0.5      0.0      0.0      4.5  \n",
       "3393      0.0      1.5      0.0      1.0      1.5  \n",
       "3394      0.0      0.0      0.0      0.0      0.5  \n",
       "3395      0.0      0.0      0.0      0.0      1.0  \n",
       "3396      0.0      0.0      0.0      0.0      0.0  "
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df = cudf.merge(df[['ID20','STATE','COUNTY','P_delta']],race_df.drop(columns=['P_diff']),on='ID20').sort_values('ID20')\n",
    "del(df,race_10,race_20,race_df,eq_10,freq_df)\n",
    "final_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "d5fbcc16-ecfe-474a-9aae-527a4d405ff4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID20</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>P_delta</th>\n",
       "      <th>R1_diff</th>\n",
       "      <th>R2_diff</th>\n",
       "      <th>R3_diff</th>\n",
       "      <th>R4_diff</th>\n",
       "      <th>R5_diff</th>\n",
       "      <th>R6_diff</th>\n",
       "      <th>R7_diff</th>\n",
       "      <th>number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3392</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>-10</td>\n",
       "      <td>-16</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3393</th>\n",
       "      <td>10010201001001</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>4</td>\n",
       "      <td>-10</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3394</th>\n",
       "      <td>10010201001002</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>-23</td>\n",
       "      <td>-21</td>\n",
       "      <td>-2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3395</th>\n",
       "      <td>10010201001003</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>-2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3396</th>\n",
       "      <td>10010201001004</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                ID20  STATE          COUNTY  P_delta  R1_diff  R2_diff  \\\n",
       "3392  10010201001000      1  Autauga County      -10      -16        2   \n",
       "3393  10010201001001      1  Autauga County        4      -10        9   \n",
       "3394  10010201001002      1  Autauga County      -23      -21       -2   \n",
       "3395  10010201001003      1  Autauga County        4        5       -2   \n",
       "3396  10010201001004      1  Autauga County        0        0        0   \n",
       "\n",
       "      R3_diff  R4_diff  R5_diff  R6_diff  R7_diff  number  \n",
       "3392        0        0        0        0        4      10  \n",
       "3393        0        2        0        1        2       4  \n",
       "3394        0        0        0        0        0      23  \n",
       "3395        0        0        0        0        1       4  \n",
       "3396        0        0        0        0        0       0  "
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df['P_delta'] = final_df.P_delta.round().astype('int32')\n",
    "final_df['number'] = final_df.P_delta.round().abs().astype('int32')\n",
    "final_df['R1_diff'] = final_df.R1_diff.round().astype('int32')\n",
    "final_df['R2_diff'] = final_df.R2_diff.round().astype('int32')\n",
    "final_df['R3_diff'] = final_df.R3_diff.round().astype('int32')\n",
    "final_df['R4_diff'] = final_df.R4_diff.round().astype('int32')\n",
    "final_df['R5_diff'] = final_df.R5_diff.round().astype('int32')\n",
    "final_df['R6_diff'] = final_df.R6_diff.round().astype('int32')\n",
    "final_df['R7_diff'] = final_df.R7_diff.round().astype('int32')\n",
    "final_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "ed3f7829-af62-46a2-838a-aeae24b67d9c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "115306867"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.number.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "0d84b8bf-8c36-400f-8b41-1b605c611a05",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8174955\n"
     ]
    }
   ],
   "source": [
    "print(len(final_df))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "2f72ccf6-421e-43ae-ad4b-554c6271be9f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n"
     ]
    }
   ],
   "source": [
    "final_df['error'] =  final_df['P_delta'] - final_df[['R1_diff','R2_diff','R3_diff','R4_diff','R5_diff','R6_diff','R7_diff']].sum(axis=1)\n",
    "final_df['R7_diff'] =  final_df['R7_diff'] + final_df['error'] # fix error by adjusting R7\n",
    "print(len(final_df[final_df['P_delta']!= final_df[['R1_diff','R2_diff','R3_diff','R4_diff','R5_diff','R6_diff','R7_diff']].sum(axis=1)]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "5d78f2c5-0717-48d9-950c-3fbb5b59a23f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "182532663"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.iloc[:,4:-2].abs().sum().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "a6138c44-50ae-4860-876b-34baba436acb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID20</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>P_delta</th>\n",
       "      <th>R1_diff</th>\n",
       "      <th>R2_diff</th>\n",
       "      <th>R3_diff</th>\n",
       "      <th>R4_diff</th>\n",
       "      <th>R5_diff</th>\n",
       "      <th>R6_diff</th>\n",
       "      <th>R7_diff</th>\n",
       "      <th>number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3392</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>-10</td>\n",
       "      <td>-16</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3393</th>\n",
       "      <td>10010201001001</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>4</td>\n",
       "      <td>-10</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3394</th>\n",
       "      <td>10010201001002</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>-23</td>\n",
       "      <td>-21</td>\n",
       "      <td>-2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                ID20  STATE          COUNTY  P_delta  R1_diff  R2_diff  \\\n",
       "3392  10010201001000      1  Autauga County      -10      -16        2   \n",
       "3393  10010201001001      1  Autauga County        4      -10        9   \n",
       "3394  10010201001002      1  Autauga County      -23      -21       -2   \n",
       "\n",
       "      R3_diff  R4_diff  R5_diff  R6_diff  R7_diff  number  \n",
       "3392        0        0        0        0        4      10  \n",
       "3393        0        2        0        1        2       4  \n",
       "3394        0        0        0        0        0      23  "
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.drop(columns=['error'],inplace=True)\n",
    "final_df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "70248405-52bd-4117-b196-eddfb274fd83",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df.to_csv('data/race_migration_diff.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "24adcfa2-c74d-42c6-ac9f-5eae2a1ced75",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>ID20</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>P_delta</th>\n",
       "      <th>R1_diff</th>\n",
       "      <th>R2_diff</th>\n",
       "      <th>R3_diff</th>\n",
       "      <th>R4_diff</th>\n",
       "      <th>R5_diff</th>\n",
       "      <th>R6_diff</th>\n",
       "      <th>R7_diff</th>\n",
       "      <th>number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3392</td>\n",
       "      <td>10010201001000</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>-10</td>\n",
       "      <td>-16</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3393</td>\n",
       "      <td>10010201001001</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>4</td>\n",
       "      <td>-10</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3394</td>\n",
       "      <td>10010201001002</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>-23</td>\n",
       "      <td>-21</td>\n",
       "      <td>-2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3395</td>\n",
       "      <td>10010201001003</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>-2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3396</td>\n",
       "      <td>10010201001004</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0            ID20  STATE          COUNTY  P_delta  R1_diff  \\\n",
       "0        3392  10010201001000      1  Autauga County      -10      -16   \n",
       "1        3393  10010201001001      1  Autauga County        4      -10   \n",
       "2        3394  10010201001002      1  Autauga County      -23      -21   \n",
       "3        3395  10010201001003      1  Autauga County        4        5   \n",
       "4        3396  10010201001004      1  Autauga County        0        0   \n",
       "\n",
       "   R2_diff  R3_diff  R4_diff  R5_diff  R6_diff  R7_diff  number  \n",
       "0        2        0        0        0        0        4      10  \n",
       "1        9        0        2        0        1        2       4  \n",
       "2       -2        0        0        0        0        0      23  \n",
       "3       -2        0        0        0        0        1       4  \n",
       "4        0        0        0        0        0        0       0  "
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "races =  cudf.read_csv('data/race_migration_diff.csv')\n",
    "races.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "8745597f-5240-4a76-a1a5-41f9ab34411e",
   "metadata": {},
   "outputs": [],
   "source": [
    "races['points']= races[['R1_diff','R2_diff','R3_diff','R4_diff','R5_diff','R6_diff','R7_diff']].abs().sum(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "ac1e53ff-6e37-49d1-99a6-adcd791e2322",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8174955"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(races)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "1519d21f-ca1c-4fb9-99ea-0c6519bc5c25",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6194258\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>ID20</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>P_delta</th>\n",
       "      <th>R1_diff</th>\n",
       "      <th>R2_diff</th>\n",
       "      <th>R3_diff</th>\n",
       "      <th>R4_diff</th>\n",
       "      <th>R5_diff</th>\n",
       "      <th>R6_diff</th>\n",
       "      <th>R7_diff</th>\n",
       "      <th>number</th>\n",
       "      <th>points</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3392</td>\n",
       "      <td>10010201001000</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>-10</td>\n",
       "      <td>-16</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3393</td>\n",
       "      <td>10010201001001</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>4</td>\n",
       "      <td>-10</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3394</td>\n",
       "      <td>10010201001002</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>-23</td>\n",
       "      <td>-21</td>\n",
       "      <td>-2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3395</td>\n",
       "      <td>10010201001003</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>-2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3397</td>\n",
       "      <td>10010201001005</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>-8</td>\n",
       "      <td>-7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0            ID20  STATE          COUNTY  P_delta  R1_diff  \\\n",
       "0        3392  10010201001000      1  Autauga County      -10      -16   \n",
       "1        3393  10010201001001      1  Autauga County        4      -10   \n",
       "2        3394  10010201001002      1  Autauga County      -23      -21   \n",
       "3        3395  10010201001003      1  Autauga County        4        5   \n",
       "5        3397  10010201001005      1  Autauga County       -8       -7   \n",
       "\n",
       "   R2_diff  R3_diff  R4_diff  R5_diff  R6_diff  R7_diff  number  points  \n",
       "0        2        0        0        0        0        4      10      22  \n",
       "1        9        0        2        0        1        2       4      24  \n",
       "2       -2        0        0        0        0        0      23      23  \n",
       "3       -2        0        0        0        0        1       4       8  \n",
       "5        0        0        0        0        0       -1       8       8  "
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "races = races[races['points']!=0]\n",
    "print(len(races))\n",
    "races.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "5e664c09-c04a-46e7-83ee-c4c52d9bac88",
   "metadata": {},
   "outputs": [],
   "source": [
    "races.drop(columns=['Unnamed: 0'],inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "b6c2f48c-9d00-4b32-8925-acb121532856",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID20</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>P_delta</th>\n",
       "      <th>R1_diff</th>\n",
       "      <th>R2_diff</th>\n",
       "      <th>R3_diff</th>\n",
       "      <th>R4_diff</th>\n",
       "      <th>R5_diff</th>\n",
       "      <th>R6_diff</th>\n",
       "      <th>R7_diff</th>\n",
       "      <th>number</th>\n",
       "      <th>points</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>-10</td>\n",
       "      <td>-16</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10010201001001</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>4</td>\n",
       "      <td>-10</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10010201001002</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>-23</td>\n",
       "      <td>-21</td>\n",
       "      <td>-2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10010201001003</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>-2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10010201001005</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>-8</td>\n",
       "      <td>-7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             ID20  STATE          COUNTY  P_delta  R1_diff  R2_diff  R3_diff  \\\n",
       "0  10010201001000      1  Autauga County      -10      -16        2        0   \n",
       "1  10010201001001      1  Autauga County        4      -10        9        0   \n",
       "2  10010201001002      1  Autauga County      -23      -21       -2        0   \n",
       "3  10010201001003      1  Autauga County        4        5       -2        0   \n",
       "5  10010201001005      1  Autauga County       -8       -7        0        0   \n",
       "\n",
       "   R4_diff  R5_diff  R6_diff  R7_diff  number  points  \n",
       "0        0        0        0        4      10      22  \n",
       "1        2        0        1        2       4      24  \n",
       "2        0        0        0        0      23      23  \n",
       "3        0        0        0        1       4       8  \n",
       "5        0        0        0       -1       8       8  "
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "races.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "5649548d-3bb5-4c0c-82a6-864388bb1c78",
   "metadata": {},
   "outputs": [],
   "source": [
    "races.to_csv('data/mapped_data_with_race.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
